﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

using System.Reflection;

namespace PersistentObjects.DAL
{
    public class Database : IDisposable
    {
        DbConnection connection;

        //DbTransaction transaction;

        public Database(string ConnectionString)
        {
            connection = new SQLiteConnection();
            connection.ConnectionString = ConnectionString;
            connection.Open();
            //transaction = connection.BeginTransaction();
        }

        #region IDisposable Members

        public void Dispose()
        {
            //transaction.Commit();
            connection.Close();
            connection.ConnectionString = "";
        }
        #endregion

        ~Database()
        {
            Dispose();
        }

        public string ConnectionString
        {
            get { return connection.ConnectionString; }
            set 
            { 
                if (connection.ConnectionString != value)
                {
                    if (connection.State == ConnectionState.Open)
                        Dispose();
                    connection.ConnectionString = value; 
                    connection.Open();
                    //transaction = connection.BeginTransaction();
                }
            }
        }

        #region "DDL"
        class TableDesc
        {
            public string Table = "";
            public struct ColumnDesc 
            {
                public ColumnDesc(string Name, System.Type Type, FieldInfo Field) { this.Name = Name; this.Type = Type; this.Field = Field; }
                public string Name; 
                public System.Type Type;
                public FieldInfo Field;
            }
            public System.Collections.Generic.List<ColumnDesc> Columns = new List<ColumnDesc>();
        }

        System.Collections.Hashtable ItemsCache = new System.Collections.Hashtable();
        TableDesc ParseItem(System.Type ItemType)
        {
            if (ItemsCache.Contains(ItemType))
            {
                return (TableDesc)ItemsCache[ItemType];
            }

            
            TableDesc ret = new TableDesc();
            ret.Table = ItemType.Name;

            FieldInfo[] fields = ItemType.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
            foreach (FieldInfo field in fields)
            {
                if (field.GetCustomAttributes(typeof(NonPersistentAttribute), false).Length == 0)
                {
                    ret.Columns.Add(new TableDesc.ColumnDesc(field.Name, field.FieldType, field));
                }
            }

            ItemsCache.Add(ItemType, ret);
            return ret;
        }

        System.Collections.Hashtable dotNetToSQLiteMap;
        string ConvertToSQLite(System.Type Type)
        {
            if (dotNetToSQLiteMap == null)
            {
                dotNetToSQLiteMap = new System.Collections.Hashtable();
                dotNetToSQLiteMap.Add(typeof(System.Int16), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Int32), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Int64), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Boolean), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Byte), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Char), "Text");
                dotNetToSQLiteMap.Add(typeof(System.DateTime), "DateTime");
                dotNetToSQLiteMap.Add(typeof(System.Decimal), "Double");
                dotNetToSQLiteMap.Add(typeof(System.Double), "Double");
                dotNetToSQLiteMap.Add(typeof(System.Guid), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.SByte), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Single), "Double");
                dotNetToSQLiteMap.Add(typeof(System.String), "Text");
                dotNetToSQLiteMap.Add(typeof(System.TimeSpan), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.UInt16), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.UInt32), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.UInt64), "Int64");
                dotNetToSQLiteMap.Add(typeof(System.Object), "Blob");
                dotNetToSQLiteMap.Add(typeof(PersistentObjects.Persistent), "Int64");
            }

            while (!dotNetToSQLiteMap.Contains(Type))
            {
                Type = Type.BaseType;
            }
            return (string)dotNetToSQLiteMap[Type];
        }

        public void CreateTable(System.Type ItemType)
        {
            TableDesc table = ParseItem(ItemType);
            StringBuilder sb = new StringBuilder ("CREATE TABLE "+table.Table+" ( ");
            foreach (TableDesc.ColumnDesc cd in table.Columns)
            {
                sb.Append(cd.Name + " " + ConvertToSQLite(cd.Type) + ", ");
            }
            sb.Length-=2;
            sb.Append(")");

            try
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = sb.ToString();
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (SQLiteException e)
            {
                //I don't know how to verify this is caused by the table being allready created
                if (e.ErrorCode != SQLiteErrorCode.Error)
                    throw e;
            }
        }

        public void DeleteTable(System.Type ItemType)
        {
            TableDesc table = ParseItem(ItemType);
            StringBuilder sb = new StringBuilder("DROP TABLE " + table.Table);
            try
            {
                DbCommand command = connection.CreateCommand();
                command.CommandText = sb.ToString();
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (SQLiteException)
            {
            }
        }
        #endregion

        public Int64 Insert<T>(T item)
        {
            try
            {
                TableDesc table = ParseItem(item.GetType());
                StringBuilder sb = new StringBuilder("INSERT INTO ");
                sb.Append(table.Table);
                sb.Append(" (");

                DbCommand command = connection.CreateCommand();

                StringBuilder sbvalues = new StringBuilder();
                foreach (TableDesc.ColumnDesc column in table.Columns)
                {
                    sb.Append(column.Name + ", ");
                    sbvalues.Append("@" + column.Name + ", ");

                    DbParameter parameter = command.CreateParameter();
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "@" + column.Name;

                    if (ConvertToSQLite(column.Type) != "Blob")
                    {
                        if (!column.Type.IsSubclassOf(typeof(Persistent)))
                            parameter.Value = column.Field.GetValue(item);
                        else
                            parameter.Value = (column.Field.GetValue(item) as Persistent).ID;
                    }
                    else
                    {
                        if (column.Type == typeof(System.Drawing.Image))
                        {
                            System.IO.MemoryStream stream = new System.IO.MemoryStream();
                            ((System.Drawing.Bitmap)column.Field.GetValue(item)).Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
                            parameter.Value = stream.ToArray();
                        }
                    }

                    command.Parameters.Add(parameter);
                }
                sb.Length -= 2;
                sbvalues.Length -= 2;

                sb.Append(") VALUES (");
                sb.Append(sbvalues + ")");
                
                command.CommandText = sb.ToString();
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();

                command.CommandText = "SELECT LAST_INSERT_ROWID()";
                return (Int64)command.ExecuteScalar();
            }
            catch (SQLiteException)
            {

                throw;
            }
        }
        
        public void Update<T>(Int64 ID, T item)
        {
            try
            {
                TableDesc table = ParseItem(item.GetType());
                StringBuilder sb = new StringBuilder("UPDATE ");
                sb.Append(table.Table);
                sb.Append(" SET ");

                DbCommand command = connection.CreateCommand();

                foreach (TableDesc.ColumnDesc column in table.Columns)
                {
                    sb.Append(column.Name + " = ");
                    sb.Append("@" + column.Name + ", ");

                    DbParameter parameter = command.CreateParameter();
                    parameter.Direction = ParameterDirection.Input;
                    parameter.ParameterName = "@" + column.Name;

                    if (ConvertToSQLite(column.Type) != "Blob")
                    {
                        if (!column.Type.IsSubclassOf(typeof(Persistent)))
                            parameter.Value = column.Field.GetValue(item);
                        else
                            parameter.Value = (column.Field.GetValue(item) as Persistent).ID;
                    }
                    else
                    {
                        if (column.Type == typeof(System.Drawing.Image))
                        {
                            System.IO.MemoryStream stream = new System.IO.MemoryStream();
                            ((System.Drawing.Bitmap)column.Field.GetValue(item)).Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
                            parameter.Value = stream.ToArray();
                        }
                    }

                    command.Parameters.Add(parameter);
                }
                sb.Length -= 2;

                sb.Append(" WHERE ROWID = ");
                sb.Append(ID);

                command.CommandText = sb.ToString();
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            catch (SQLiteException)
            {
                throw;
            }
        }
        //TODO UNTESTED
        public void Delete(Int64 ID, System.Type type)
        {
            TableDesc table = ParseItem(type);
            StringBuilder sb = new StringBuilder("DELETE FROM ");

            sb.Append(table.Table);
            sb.Append(" WHERE ROWID = ");
            sb.Append(ID);

            DbCommand command = connection.CreateCommand();
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            command.ExecuteNonQuery();
        }

        #region FindClauses

        public List<Int64> FindIDs(System.Type type)
        {
            return FindIDs(type, null, null);
        }

		public List<Int64> FindIDs<T>(T Example)
        {
            TableDesc table = ParseItem(Example.GetType());
            StringBuilder sb = new StringBuilder("SELECT ROWID FROM ");
            sb.Append(table.Table);
            sb.Append(" WHERE ");
            DbCommand command = connection.CreateCommand();
            foreach (TableDesc.ColumnDesc column in table.Columns)
            {
                string result = ConvertToSQLite(column.Type);
                string operation = " = ";
                object obj = 0;
                if (result == "Blob") continue;
                if (result == "Int64")
                {
                    obj = (Int64)column.Field.GetValue(Example);
                    if ((Int64)obj == 0) continue;

                }
                if (result == "Text")
                {
                    obj = (string)column.Field.GetValue(Example);
                    if ((String)obj == "") continue;
                    operation = " LIKE ";
                }
                if (result == "DateTime")
                {
                    obj = (DateTime)column.Field.GetValue(Example);
                    if ((DateTime)obj == DateTime.MaxValue) continue;
                }
                if (result == "Double")
                {
                    obj = (Double)column.Field.GetValue(Example);
                    if ((double)obj == 0D) continue;
                }
                DbParameter parameter = command.CreateParameter();
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "@" + column.Name;
                parameter.Value = obj;

                sb.Append(column.Name);
                sb.Append(operation);
                sb.Append("@" + column.Name + " AND ");

                command.Parameters.Add(parameter);
            }
            sb.Length -= 5;
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            DbDataReader reader = command.ExecuteReader();
            System.Collections.Generic.List<Int64> ret = new List<Int64>();
            while (reader.Read())
            {
                ret.Add(reader.GetInt64(0));
            }
            reader.Close();

            return ret;
        }
  
        public List<Int64> FindIDs(System.Type type, SelectWhereClause[] WhereClauses)
        {
            return FindIDs(type, WhereClauses, null);
        }

        public List<Int64> FindIDs(System.Type type, SelectOrderByClause[] OrderByClauses)
        {
            return FindIDs(type, null, OrderByClauses);
        }

        public List<Int64> FindIDs(System.Type type, SelectWhereClause[] WhereClauses, SelectOrderByClause[] OrderByClauses)
        {
            TableDesc table = ParseItem(type);
            StringBuilder sb = new StringBuilder("SELECT ROWID FROM ");
            sb.Append(table.Table);
            DbCommand command = connection.CreateCommand();

            ParseWhereClauses(WhereClauses, command, sb);
            ParseOrderByClauses(OrderByClauses, sb);

            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            DbDataReader reader = command.ExecuteReader();
            System.Collections.Generic.List<Int64> ret = new List<Int64>();
            while (reader.Read())
            {
                ret.Add(reader.GetInt64(0));
            }
            reader.Close();

            return ret;
        }

        public DatabaseReader FindItems(System.Type type)
        {
            return FindItems(type, null, null);
        }

        public DatabaseReader FindItems<T>(T Example)
        {
            TableDesc table = ParseItem(Example.GetType());
            StringBuilder sb = new StringBuilder("SELECT *, ROWID FROM ");
            sb.Append(table.Table);
            sb.Append(" WHERE ");
            DbCommand command = connection.CreateCommand();
            foreach (TableDesc.ColumnDesc column in table.Columns)
            {
                string result = ConvertToSQLite(column.Type);
                string operation = " = ";
                object obj = 0;
                if (result == "Blob") continue;
                if (result == "Int64")
                {
                    obj = (Int64)column.Field.GetValue(Example);
                    if ((Int64)obj == 0) continue;

                }
                if (result == "Text")
                {
                    obj = (string)column.Field.GetValue(Example);
                    if ((String)obj == "") continue;
                    operation = " LIKE ";
                }
                if (result == "DateTime")
                {
                    obj = (DateTime)column.Field.GetValue(Example);
                    if ((DateTime)obj == DateTime.MaxValue) continue;
                }
                if (result == "Double")
                {
                    obj = (Double)column.Field.GetValue(Example);
                    if ((double)obj == 0D) continue;
                }
                DbParameter parameter = command.CreateParameter();
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "@" + column.Name;
                parameter.Value = obj;

                sb.Append(column.Name);
                sb.Append(operation);
                sb.Append("@" + column.Name + " AND ");

                command.Parameters.Add(parameter);
            }
            sb.Length -= 5;
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            DatabaseReader reader = new DatabaseReader(command.ExecuteReader());
            return reader;
        }

        public DatabaseReader FindItems(System.Type type, SelectWhereClause[] WhereClauses)
        {
            return FindItems(type, WhereClauses, null);
        }

        public DatabaseReader FindItems(System.Type type, SelectOrderByClause[] OrderByClauses)
        {
            return FindItems(type, null, OrderByClauses);
        }

        public DatabaseReader FindItems(System.Type type, SelectWhereClause[] WhereClauses, SelectOrderByClause[] OrderByClauses)
        {
            TableDesc table = ParseItem(type);
            StringBuilder sb = new StringBuilder("SELECT *, ROWID FROM ");
            sb.Append(table.Table);
            DbCommand command = connection.CreateCommand();

            ParseWhereClauses(WhereClauses, command, sb);
            ParseOrderByClauses(OrderByClauses, sb);

            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            DatabaseReader reader = new DatabaseReader(command.ExecuteReader());
            return reader;
        }

        private static void ParseWhereClauses(SelectWhereClause[] WhereClauses, DbCommand command, StringBuilder sb)
        {
            if (WhereClauses == null) return;
            if (WhereClauses.Length == 0) return;
            sb.Append(" WHERE ");
            for (int i = 0; i < WhereClauses.Length; i++)
            {
                sb.Append(WhereClauses[i].Field + " " + WhereClauses[i].Operator + " @" +
                    i.ToString() + " AND ");

                DbParameter parameter = command.CreateParameter();
                parameter.Direction = ParameterDirection.Input;
                parameter.ParameterName = "@" + i.ToString();
                parameter.Value = WhereClauses[i].Value;

                command.Parameters.Add(parameter);
            }
            sb.Length -= 5;
        }

        private static void ParseOrderByClauses(SelectOrderByClause[] OrderByClauses, StringBuilder sb)
        {
            if (OrderByClauses == null) return;
            if (OrderByClauses.Length == 0) return;
            sb.Append(" ORDER BY ");
            for (int i = 0; i < OrderByClauses.Length; i++)
            {
                sb.Append(OrderByClauses[i].Field + " " + OrderByClauses[i].Operator + ", ");
            }
            sb.Length -= 2;
        }
        #endregion

        public void Retrieve(Int64 ID, System.Type type, object Item)
        {
            TableDesc table = ParseItem(type);
            StringBuilder sb = new StringBuilder("SELECT ");

            foreach (TableDesc.ColumnDesc column in table.Columns)
            {
                sb.Append(column.Name + ", ");
            }
            sb.Length -= 2;
            sb.Append(" FROM ");
            sb.Append(table.Table);
            sb.Append(" WHERE ROWID = ");
            sb.Append(ID);


            DbCommand command = connection.CreateCommand();
            command.CommandText = sb.ToString();
            command.CommandType = CommandType.Text;

            DbDataReader reader = command.ExecuteReader();
            reader.Read();

            Retrieve(table, reader, Item);
            reader.Close();
        }

        public object Retrieve(Int64 ID, System.Type type)
        {
            ConstructorInfo constructor = type.GetConstructor(new Type[0]);
            object ret = constructor.Invoke(new object[0]);

            Retrieve(ID, type, ret);
            return ret;
        }

        public void Retrieve(DatabaseReader Reader, System.Type type, object Item)
        {
            Retrieve(ParseItem(type), Reader, Item);
        }

        public object Retrieve(DatabaseReader Reader, System.Type type)
        {
            ConstructorInfo constructor = type.GetConstructor(new Type[0]);
            object ret = constructor.Invoke(new object[0]);

            Retrieve(ParseItem(type), Reader, ret);
            return ret;
        }

        private void Retrieve(TableDesc table, IDataReader reader, object Item)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                if (ConvertToSQLite(table.Columns[i].Type) != "Blob")
                {
                    if (!table.Columns[i].Type.IsSubclassOf(typeof(Persistent)))
                        table.Columns[i].Field.SetValue(Item, reader.GetValue(i));
                    else
                    {
                        //Persistent pers = (Persistent)table.Columns[i].Type.GetConstructor(new Type[]{typeof(Int64)}).Invoke(new object[]{reader.GetValue(i)});
                        Persistent pers = PersistentObjects.Persistent.RetrievePersistentObject (reader.GetInt64(i), table.Columns[i].Type);
                        table.Columns[i].Field.SetValue(Item, pers);
                    }
                }
                else
                {
                    if (table.Columns[i].Type == typeof(System.Drawing.Image))
                    {
                        byte[] bytes = (byte[])reader.GetValue(i);
                        System.IO.MemoryStream stream = new System.IO.MemoryStream(bytes, false);
                        table.Columns[i].Field.SetValue(Item, new System.Drawing.Bitmap(stream));
                    }
                }
            }
        }

        public int ItemHashCode<T>(T item)
        {
            TableDesc table = ParseItem(item.GetType());

            int ret = 0;
            foreach (TableDesc.ColumnDesc column in table.Columns)
            {
                object field = column.Field.GetValue(item);
                ret ^= field == null? 0: field.GetHashCode();
            }
            return ret;
        }

    }

    public class SelectWhereClause
    {
        public SelectWhereClause(string Field, string Operator, object Value)
        {
            field = Field;
            oper = Operator;
            val = Value;
        }

        public SelectWhereClause(string Field, object Value)
        {
            field = Field;
            val = Value;
            string str = Value as string;
            if (str == null)
                oper = "=";
            else
                if (str.IndexOf("%") == -1)
                    oper = "=";
                else
                    oper = "LIKE";
        }

        string field;
        public string Field
        {
            get { return field; }
        }

        string oper;
        public string Operator
        {
            get { return oper; }
        }

        object val;
        public object Value
        {
            get { return val; }
        }
    }

    public class SelectOrderByClause
    {
        public SelectOrderByClause(string Field, bool Ascending)
        {
            field = Field;
            oper = Ascending;
        }

        public SelectOrderByClause(string Field)
            :this (Field, false)
        {
        }

        string field;
        public string Field
        {
            get { return field; }
        }

        bool oper;
        public string Operator
        {
            get { return oper ? "ASC" : "DESC"; }
        }
    }

    public class DatabaseReader : IDataReader
    {
        IDataReader reader;

        public DatabaseReader(IDataReader Reader)
        {
            reader = Reader;
        }

        #region IDataReader Members

        public void Close()
        {
            reader.Close();
        }

        public int Depth
        {
            get { return reader.Depth; }
        }

        public DataTable GetSchemaTable()
        {
            return reader.GetSchemaTable();
        }

        public bool IsClosed
        {
            get { return reader.IsClosed; }
        }

        public bool NextResult()
        {
            return reader.NextResult();
        }

        public bool Read()
        {
            return reader.Read();
        }

        public int RecordsAffected
        {
            get { return reader.RecordsAffected; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            reader.Dispose();
        }

        #endregion

        #region IDataRecord Members

        public int FieldCount
        {
            get { return reader.FieldCount; }
        }

        public bool GetBoolean(int i)
        {
            return reader.GetBoolean(i);
        }

        public byte GetByte(int i)
        {
            return reader.GetByte(i);
        }

        public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
        {
            return reader.GetBytes(i, fieldOffset, buffer, bufferoffset, length);
        }

        public char GetChar(int i)
        {
            return reader.GetChar(i);
        }

        public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
        {
            return reader.GetChars(i, fieldoffset, buffer, bufferoffset, length);
        }

        public IDataReader GetData(int i)
        {
            return reader.GetData(i);
        }

        public string GetDataTypeName(int i)
        {
            return reader.GetDataTypeName(i);
        }

        public DateTime GetDateTime(int i)
        {
            return reader.GetDateTime(i);
        }

        public decimal GetDecimal(int i)
        {
            return reader.GetDecimal(i);
        }

        public double GetDouble(int i)
        {
            return reader.GetDouble(i);
        }

        public Type GetFieldType(int i)
        {
            return reader.GetFieldType(i);
        }

        public float GetFloat(int i)
        {
            return reader.GetFloat(i);
        }

        public Guid GetGuid(int i)
        {
            return reader.GetGuid(i);
        }

        public short GetInt16(int i)
        {
            return reader.GetInt16(i);
        }

        public int GetInt32(int i)
        {
            return reader.GetInt32(i);
        }

        public long GetInt64(int i)
        {
            return reader.GetInt64(i);
        }

        public string GetName(int i)
        {
            return reader.GetName(i);
        }

        public int GetOrdinal(string name)
        {
            return reader.GetOrdinal(name);
        }

        public string GetString(int i)
        {
            return reader.GetString(i);
        }

        public object GetValue(int i)
        {
            return reader.GetValue(i);
        }

        public int GetValues(object[] values)
        {
            return reader.GetValues(values);
        }

        public bool IsDBNull(int i)
        {
            return reader.IsDBNull(i);
        }

        public object this[string name]
        {
            get { return reader[name]; }
        }

        public object this[int i]
        {
            get { return reader[i]; }
        }

        #endregion
    }

}
